import os
import sqlite3
import logging
from typing import List, Dict, Optional, Tuple
from utils import nas_sqlite_dirdb_get

# 配置日志
logger = logging.getLogger(__name__)

def folder_folder_db_query(corpid: str, path: str, sql: str, params: tuple = (), count_sql: str = None) -> Tuple[List[tuple], int, Optional[str]]:
    """执行数据库查询并返回结果"""
    results = []
    totalnum = 0
    error = None
    
    try:
        conn = nas_sqlite_dirdb_get(corpid, path)
        if conn:
            cursor = conn.cursor()
            
            # 执行主查询
            cursor.execute(sql, params)
            results = cursor.fetchall()
            
            # 如果提供了计数查询，执行计数
            if count_sql:
                cursor.execute(count_sql, params[:2])  # 假设前两个参数是uid和subid
                totalnum = cursor.fetchone()[0]
            
            cursor.close()
            conn.close()
    except Exception as e:
        error = str(e)
        logger.error(f"查询执行失败: {e}")
    
    return results, totalnum, error

def get_all_subdirectories(corpid: str, parent_path: str) -> List[str]:    
    """递归获取所有子目录路径
    
    参数:
    - corpid: 企业ID
    - parent_path: 父路径
    
    返回:
    - 所有子目录的完整路径列表
    """
    subdirs = []
    visited = set()  # 用于记录已访问的路径，防止循环引用
    
    # 使用内部函数实现递归逻辑
    def _collect_subdirectories(path):
        # 检查是否已访问过该路径，防止循环
        if path in visited:
            return
        visited.add(path)
        
        try:
            # 获取数据库连接
            sqlite_conn = nas_sqlite_dirdb_get(corpid, path)
            if sqlite_conn:
                cursor = sqlite_conn.cursor()
                try:
                    # 从file_info表查询所有子目录
                    cursor.execute("SELECT name, fpath FROM file_info WHERE filetype = 0 AND delflag = 0 ")                    
                    
                    # 获取子目录并递归处理
                    for row in cursor.fetchall():
                        child_name = row[0]
                        # 构建完整的子目录路径
                        child_path = os.path.join(path, child_name)
                        subdirs.append(child_path)  # 将子目录添加到结果列表
                        # 递归调用，获取该子目录下的所有子目录
                        _collect_subdirectories(child_path)
                except Exception as e:
                    print(f"获取子目录失败: {e}")
                finally:
                    cursor.close()
                    sqlite_conn.close()
        except Exception as e:
            print(f"递归获取子目录失败: {e}")
    
    # 开始递归收集所有子目录
    _collect_subdirectories(parent_path)
    
    return subdirs

def search_files_and_folders(corpid: str, subid: str, uid: str, path: str, 
                            key: str, type: int, flag: int) -> Dict[str, any]:
    """
    搜索文件和文件夹
    
    参数:
    - corpid: 企业ID
    - subid: 子账户ID
    - uid: 用户ID
    - path: 搜索路径
    - key: 搜索关键词
    - type: 搜索类型 (0: 文件夹, 1: 文件, 2: 所有, 3: fid)
    - flag: 递归标志位 (3: 递归搜索)
    
    返回:
    - 包含搜索结果的字典
    """
    # 验证必要参数
    if not corpid or not subid or not uid or not path:
        return {"code": 400, "msg": "缺少必要参数"}
    
    # 构建查询条件
    search_keyword = f"%{key}%" if key else "%"
    
    # 获取所有需要搜索的路径列表
    search_paths = [path]  # 至少包含当前路径
    
    # 如果是递归搜索，获取所有子目录
    # 先获取path下面的所有子目录的sqlite，不需要匹配，全部获取
    if flag == 3:
        subdirectories = get_all_subdirectories(corpid, path)
        search_paths.extend(subdirectories)
    
    # 构建基础查询语句 - 根据类型构建查询逻辑
    # 文件和文件夹名称都在file_info表的name字段
    if type == 0:  # 只搜索文件夹
        query = """
            SELECT fid, name, codetime, fsize, nodeid, pid, subid, filetype, ctime, resolvepower, mimeid, fpath 
            FROM file_info 
            WHERE filetype = 0 AND delflag = 0 
        """
        where_clause = "name LIKE ?"
        params = [search_keyword]
        
    elif type == 1:  # 只搜索文件
        query = """
            SELECT fid, name, codetime, fsize, nodeid, pid, subid, filetype, ctime, resolvepower, mimeid, fpath 
            FROM file_info 
            WHERE filetype != 0 AND delflag = 0 
        """
        where_clause = "name LIKE ?"
        params = [search_keyword]
        
    elif type == 3:  # 搜索fid
        query = """
            SELECT fid, name, codetime, fsize, nodeid, pid, subid, filetype, ctime, resolvepower, mimeid, fpath 
            FROM file_info 
            WHERE delflag = 0 
        """
        where_clause = "fid LIKE ?"
        params = [search_keyword]
        
    else:  # 搜索所有（type=2）
        query = """
            SELECT fid, name, codetime, fsize, nodeid, pid, subid, filetype, ctime, resolvepower, mimeid, fpath 
            FROM file_info 
            WHERE delflag = 0 
        """
        where_clause = "name LIKE ?"
        params = [search_keyword]
    
    # 添加WHERE子句
    if where_clause:
        query += " AND " + where_clause
    
    # 存储所有搜索结果
    all_results = []
    
    # 遍历这些sql路径去匹配文件、文件夹、fid或所有
    for search_path in search_paths:
        # 复制基础查询和参数
        path_query = query
        path_params = params.copy()
        
        # 添加路径过滤条件 - 精确匹配当前路径
        path_query += " AND fpath = ?"
        path_params.append(search_path)
        
        # 使用通用查询函数执行查询
        results, _, error = folder_folder_db_query(
            corpid, 
            path=search_path, 
            sql=path_query, 
            params=path_params
        )
        
        if error:
            print(f"搜索查询失败: {error}")
            continue
        
        # 格式化结果
        for row in results:
            # 确保返回一致的JSON格式
            row_dict = {
                "fid": row[0] or "",
                "name": row[1],
                "codetime": row[2],
                "fsize": row[3],
                "nodeid": row[4],
                "pid": row[5],
                "subid": row[6],
                "filetype": row[7],
                "ctime": row[8],
                "resolvepower": row[9],
                "mimeid": row[10],
                "path": row[11]  # 直接使用fpath作为path
            }
            all_results.append(row_dict)
    
    return {"code": 200, "data": all_results, "total": len(all_results)}